package gx.dao.impl.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.jdbc.ReturningWork;
import org.springframework.stereotype.Repository;

import gx.dao.SaleItemDao;
import gx.dao.impl.common.BaseDaoImpl;
import gx.entity.SaleItem;

@Repository
public class SaleItemDaoImpl extends BaseDaoImpl<SaleItem> implements SaleItemDao {

	

	@Override
	public Map<String, Long> totaInventory(final int year) {
		//出库 status = 2
				final String sql = "select month(p.sale_date) m,sum(pi.num) "
						        + "n from t_sale_item pi join "
						        + "t_sale p on pi.sale_id = p.id "
						        + "where p.status = 2 and year(p.sale_date)= ? "
						        + "group by month(p.sale_date)";
				Map<String, Long> map = getSession().doReturningWork(
						new ReturningWork<Map<String, Long>>() {

							@Override
							public Map<String, Long> execute(Connection conn) throws SQLException {
								Map<String, Long> mps = new LinkedHashMap<String,Long>();
								PreparedStatement ps =(PreparedStatement) conn.prepareStatement(sql);
								ps.setInt(1, year);
								ResultSet rs = ps.executeQuery();
								while(rs.next()){
									String month = rs.getString(1);
									Long num = rs.getLong(2);
									mps.put(month, num);
								} 
								return mps;
							}
						});
				return map;
	}

	@Override
	public Map<String, Long> totaInventoryByCategory(final int year) {
		//出库 status = 2
		final String sql = "select c.name m,sum(pi.num) "
				        + "n from t_sale_item pi "
				        + "join t_sale p on pi.sale_id = p.id "
				        + "join t_product pr on pi.prod_id = pr.id "
				        + "join t_category c on pr.cate_id = c.id "
				        + "where p.status = 2 and year(p.sale_date)= ? "
				        + "group by c.id";
		Map<String, Long> map = getSession().doReturningWork(
				new ReturningWork<Map<String, Long>>() {

					@Override
					public Map<String, Long> execute(Connection conn) throws SQLException {
						Map<String, Long> mps = new LinkedHashMap<String,Long>();
						PreparedStatement ps =(PreparedStatement) conn.prepareStatement(sql);
						ps.setInt(1, year);
						ResultSet rs = ps.executeQuery();
						while(rs.next()){
							String name = rs.getString(1);
							Long num = rs.getLong(2);
							mps.put(name, num);
						} 
						return mps;
					}
				});
		return map;
	}

	@Override
	public List<SaleItem> findBySaleId(Integer id) {
		return find("from SaleItem where sale_id = ? ",id);
	}

}
